package com.example.myapplication.utils;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.myapplication.bean.UserBean;
import com.example.myapplication.bean.VideoBean;
import com.example.myapplication.sqlite.SQLiteHelper;

import java.util.ArrayList;
import java.util.List;

public class DBUtils {//专门用于读取和更改用户在数据库中的信息
    private static DBUtils instance = null;
    private static SQLiteHelper helper;//数据库对象
    private static SQLiteDatabase db;
    public DBUtils(Context context) {
       helper=new SQLiteHelper(context);
       db=helper.getWritableDatabase();//获取可读写的数据库
    }
    public static DBUtils getInstance(Context context) {//单例模式
        if (instance == null) {
            instance = new DBUtils(context);
        }
        return instance;
    }
    /**
     * 保存个人资料信息
     */
    public void saveUserInfo(UserBean bean) {
        ContentValues cv=new ContentValues();//ContentValues用来存储数据，key只能是String类型，values只能存储基本类型的数据，
                                  // 像string，int之类的，不能存储对象这种东西。ContentValues 常用在数据库中的操作。
        cv.put("userName",bean.userName);
        cv.put("nickName",bean.nickName);
        cv.put("sex",bean.sex);
        cv.put("signature",bean.signature);
        db.insert(SQLiteHelper.U_USERINFO,null,cv);//insert的一种构造方法
                       //public long insert (String table, String nullColumnHack, ContentValues values)
        //当values参数为空或者里面没有内容的时候，insert是会失败的(底层数据库不允许插入一个空行)
        /*如果插入数据为空：
        若不添加nullColumnHack则sql语句最终的结果将会类似insert into tableName()values();这是不允许的。
        若添加上nullColumnHack则sql语句将会变成insert into tableName (nullColumnHack)values(null);这是可以的。*/
    }
    /**
     * 获取个人资料信息
     */
    public UserBean getUserInfo(String userName) {
        String sql = "SELECT * FROM " + SQLiteHelper.U_USERINFO + " WHERE userName=?";
        Cursor cursor = db.rawQuery(sql,new String[]{userName});//cursor游标,rawQuery第二个参数必须为null或字符串数组
        //上面两个连接即为查询语句
        UserBean bean = null;
        while (cursor.moveToNext()) {//moveToNext()移动光标到下一行,遍历
            bean = new UserBean();
            bean.userName=cursor.getString(cursor.getColumnIndex("userName"));//getColumnIndex(String columnName)
                                                                         // 返回指定列的名称，如果不存在返回-1
            bean.nickName=cursor.getString(cursor.getColumnIndex("nickName"));
            bean.sex=cursor.getString(cursor.getColumnIndex("sex"));
            bean.signature=cursor.getString(cursor.getColumnIndex("signature"));
        }
        cursor.close();//关闭游标
        return bean;
    }
    /**
     * 修改个人资料
     */
    public void updateUserInfo(String key, String value, String userName) {
        ContentValues cv=new ContentValues();
        cv.put(key,value);
        db.update(SQLiteHelper.U_USERINFO,cv,"userName=?",new String[]{userName});
    }
    /**
     * 保存视频播放记录
     */
    public void saveVideoPlayList(VideoBean bean, String userName) {
        // 判断如果里面有此播放记录则需删除重新存放
        if (hasVideoPlay(bean.chapterId, bean.videoId,userName)) {
            // 删除之前存入的播放记录
            boolean isDelete=delVideoPlay(bean.chapterId,bean.videoId,userName);
            if(!isDelete){
                //没有删除成功时，则需跳出此方法不再执行下面的语句
                return;
            }
        }
       ContentValues cv=new ContentValues();
       cv.put("userName",userName);
       cv.put("chapterId",bean.chapterId);
       cv.put("videoId",bean.videoId);
       cv.put("videoPath",bean.videoPath);
       cv.put("title",bean.title);
       cv.put("secondTitle",bean.secondTitle);
       db.insert(SQLiteHelper.U_VIDEO_PLAY_LIST,null,cv);
    }
    /**
     * 判断视频记录是否存在
     */
    public boolean hasVideoPlay(int chapterId, int videoId,String userName) {
        boolean hasVideo = false;
        String sql="SELECT * FROM "+SQLiteHelper.U_VIDEO_PLAY_LIST+" WHERE chapterId=? AND videoId=? AND userName=?";
        Cursor cursor=db.rawQuery(sql,new String[]{ chapterId + "",videoId +"",userName});
        if (cursor.moveToFirst()) {
            hasVideo = true;
        }
        cursor.close();
        return hasVideo;
    }
    /**
     * 删除已经存在的视频记录
     */
    public boolean delVideoPlay(int chapterId, int videoId,String userName) {
        boolean delSuccess=false;
        int row=db.delete(SQLiteHelper.U_VIDEO_PLAY_LIST,
                " chapterId=? AND videoId=? AND userName=?",new String[]{chapterId+"",videoId+"",userName=""});
        if (row > 0) {
            delSuccess=true;
        }
        return delSuccess;
    }
    public List<VideoBean> getVideoHistory(String userName){
        String sql="SELECT * FROM "+SQLiteHelper.U_VIDEO_PLAY_LIST+" WHERE userName=?";
        Cursor cursor=db.rawQuery(sql,new String[]{userName});
        List<VideoBean> vbl=new ArrayList<VideoBean>();
        VideoBean bean=null;
        while (cursor.moveToNext()){
            bean=new VideoBean();
            bean.chapterId=cursor.getInt(cursor.getColumnIndex("chapterId"));
            bean.videoId=cursor.getInt(cursor.getColumnIndex("videoId"));
            bean.videoPath=cursor.getString(cursor.getColumnIndex("videoPath"));
            bean.title=cursor.getString(cursor.getColumnIndex("title"));
            bean.secondTitle=cursor.getString(cursor.getColumnIndex("secondTitle"));
            vbl.add(bean);
            bean=null;
        }
        cursor.close();
        return vbl;
    }
}